package ru.arriah.servicedesk.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.ejb.EJBException;
import javax.ejb.SessionContext;
import javax.sql.DataSource;

import ru.arriah.common.ejb.GenericSessionBean;
import ru.arriah.servicedesk.bean.ClientBean;
import ru.arriah.servicedesk.bean.DepartmentBean;
import ru.arriah.servicedesk.bean.EmployeeBean;
import ru.arriah.servicedesk.bean.GroupBean;
import ru.arriah.servicedesk.bean.IntWrapper;
import ru.arriah.servicedesk.bean.MainReportBean;
import ru.arriah.servicedesk.bean.OrganizationBean;
import ru.arriah.servicedesk.bean.ReportBean;
import ru.arriah.servicedesk.bean.RequestBean;
import ru.arriah.servicedesk.bean.RequestHistoryBean;
import ru.arriah.servicedesk.bean.RequestPriorityBean;
import ru.arriah.servicedesk.bean.RequestStatusBean;
import ru.arriah.servicedesk.bean.RequestTypeBean;
import ru.arriah.servicedesk.bean.TaskBean;
import ru.arriah.servicedesk.bean.TaskStatusBean;
import ru.arriah.servicedesk.bean.UserBean;
import ru.arriah.servicedesk.help.DbUtils;
import ru.arriah.servicedesk.help.Utils;

public class RequestManager extends GenericSessionBean {
   protected DataSource dataSource;
   
   enum RequestOrderByEnum {
     N,
     status, 
     registrationdate, 
     regnumber, 
     client, 
     organization
   };   
   
   enum TaskOrderByEnum {
     N,
     status, 
     startdate,
     enddate,
     approxenddate,
     priority,
     executor
   };
   
   
   
   public RequestManager() {
      super();
      dataSource = null;      
   }   


   
   public void setSessionContext(
      SessionContext sessionContext
   ) throws EJBException, RemoteException {
      super.setSessionContext(sessionContext);

      try {
         this.dataSource = Utils.getDataSource();
      } catch (Exception e) {
         handleException(e, "setSessionContext");
         throw new EJBException(e);
      }
   }
   
   
   
   private void setId (
     PreparedStatement preparedStatement,
     int position,
     int value
   ) throws SQLException  {
     if (value==0) 
       preparedStatement.setNull(position, Types.INTEGER);
     else preparedStatement.setInt(position, value);
   }
   
   
   
   private RequestBean resultSetToBean(ResultSet resultSet) {
    try {
      
      RequestBean requestBean = new RequestBean(resultSet);
      UserBean clientBean = new UserBean(resultSet);      
      OrganizationBean organizationBean = new OrganizationBean(resultSet);
      RequestStatusBean requestStatusBean = new RequestStatusBean(resultSet);

      requestBean.setRequestStatus(requestStatusBean);      
      requestBean.setOrganization(organizationBean);      
      requestBean.setClient(clientBean);
      
      return requestBean;
      
    } catch (Exception e) {
      handleException(e, "resultSetToBean");
      throw new EJBException(e);
    }
  }
  
   
   
  private TaskBean resultSetToBeanTask(ResultSet resultSet) {
    try {
      TaskBean taskBean = new TaskBean();
      TaskStatusBean taskStatusBean = new TaskStatusBean(); 
      
      taskStatusBean.fillFromResultSet(resultSet);
      taskBean.setStatus(taskStatusBean);      
      taskBean.fillFromResultSet(resultSet);      
      
      return taskBean;
      
    } catch (Exception e) {
      handleException(e, "resultSetToBeanTask");
      throw new EJBException(e);
    }
  }
   
   
   
   private Collection<RequestBean> resultSetToCollection(ResultSet resultSet) {
      try {
         ArrayList<RequestBean> requestsList = new ArrayList<RequestBean>();
         while (resultSet.next()) {
            requestsList.add(resultSetToBean(resultSet));
         }
         return requestsList;
      } catch (Exception e) {
         handleException(e, "resultSetToCollection");
         throw new EJBException(e);
      }
   }       
   
   
   
   private Collection<RequestBean> resultSetToCollection(
      ResultSet resultSet,
      int page, 
      int itemsPerPage, 
      IntWrapper records
   ) {
      try {
         ArrayList<RequestBean> requestsList = 
            new ArrayList<RequestBean>();

         if (!resultSet.next())
            return requestsList;

         int itemCount = itemsPerPage;

         resultSet.last();
         records.setValue(resultSet.getRow());
         int pages = (records.getValue() / itemCount);
         if (records.getValue() % itemCount != 0) {
            pages++;
         }

         resultSet.absolute(page * itemCount + 1);

         int i = 0;
         while (i < itemCount && !resultSet.isAfterLast()) {
            requestsList.add(resultSetToBean(resultSet));
            resultSet.next();
            i++;
         }

         return requestsList;
      } catch (Exception e) {
         handleException(e, "resultSetToCollection");
         throw new EJBException(e);
      }
   }
   
   
   
   private Collection<TaskBean> resultSetToTaskCollection(ResultSet resultSet) {
      try {
         ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();
         while (resultSet.next()) {
            tasksList.add(resultSetToBeanTask(resultSet));
         }
         return tasksList;
      } catch (Exception e) {
         handleException(e, "resultSetToTaskCollection");
         throw new EJBException(e);
      }
   }
   
   
   
   private Collection<TaskBean> resultSetToTaskCollection(
      ResultSet resultSet,
      int page, 
      int itemsPerPage, 
      IntWrapper records
   ) {
      try {
         ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();

         if (!resultSet.next()) {
            return tasksList;
         }

         int itemCount = itemsPerPage;

         resultSet.last();
         records.setValue(resultSet.getRow());
         int pages = (records.getValue() / itemCount);
         if (records.getValue() % itemCount != 0) {
            pages++;
         }        

         resultSet.absolute(page * itemCount + 1);

         int i = 0;
         while (i < itemCount && !resultSet.isAfterLast()) {
            tasksList.add(resultSetToBeanTask(resultSet));
            resultSet.next();
            i++;
         }

         return tasksList;
      } catch (Exception e) {
         handleException(e, "resultSetToTaskCollection");
         throw new EJBException(e);
      }
   }

   
   
   /**
    * simple select request by id
    * 
    * @param requestId
    * @return RequestBean
    */
   public RequestBean selectRequest(int requestId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       RequestBean requestBean = new RequestBean();
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `request`" +            
           " WHERE `request`.`id` = ?"           
       );
       
       preparedStatement.setInt(1, requestId);
             
       resultSet = preparedStatement.executeQuery();
       
       if(resultSet.next()) {
         requestBean.fillFromResultSet(resultSet);
       }
       
       return requestBean;       
     } catch (Exception e){
        handleException(e, "selectRequest");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }
   
   
   public RequestBean selectRequestByTaskId(int taskId) {
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {
        RequestBean requestBean = new RequestBean();
        connection = dataSource.getConnection();
        
        preparedStatement = connection.prepareStatement(
            "SELECT * FROM" +
            " `request`, " +
            " `task`" +            
            " WHERE `task`.`request_id` = `request`.`id`" +
            " AND `task`.`id` = ?"
        );
        
        preparedStatement.setInt(1, taskId);
              
        resultSet = preparedStatement.executeQuery();
        
        if(resultSet.next()) {
          requestBean.fillFromResultSet(resultSet);
        }
        
        return requestBean;       
      } catch (Exception e){
         handleException(e, "selectRequestByTaskId");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
       }
    }

   
   
   public Collection<RequestBean> selectRequestsByStatus(int statusId) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       Collection<RequestBean> requestList;
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`status_id` = ?"
       );
             
       preparedStatement.setInt(1,statusId);
       resultSet = preparedStatement.executeQuery();
       
       requestList = resultSetToCollection(resultSet);
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequestsByStatus");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }     
   }
   
   
   
   public Collection<RequestBean> selectRequests() {
     return this.selectRequests("", "ASC");
   }
   
   
   
   public Collection<RequestBean> selectRequests(
     String orderBy, 
     String order
   ) {
     
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
     
     try {
       Collection<RequestBean> requestList;
       connection = dataSource.getConnection();
              
       order = (order.equals("ASC") ? "ASC" : "DESC");
       
       try {         
         RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
         switch (orderEnum) {
         case N:
           orderBy = "`request`.`id`";
           break;
         case client:
           orderBy = "`user`.`lastname`";
           break;
         case organization:  
           orderBy = "`organization`.`shorttitle`";
           break;                    
         case status:
           orderBy = "`requeststatus`.`request_status`";
           break;
         case registrationdate:
           orderBy = "`request`.`registrationdate`";
           break;
         default:
           orderBy = "`request`.`id`";
           break;
         }
       } catch (Exception e) {
         orderBy = "`request`.`id`";
       }
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " ORDER BY " + orderBy + " " + order
       );
                    
       resultSet = preparedStatement.executeQuery();
       
       requestList = resultSetToCollection(resultSet);
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequests");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(
           connection, 
           preparedStatement, 
           resultSet
        );
     }
   }
   
   
   
   public Collection<RequestBean> selectRequests(
     String orderBy,
     String order,
     int page,
     int itemsPerPage,
     IntWrapper records     
   ) {
     return this.selectRequests(
       orderBy,
       order,
       page,
       itemsPerPage,
       records,
       "'1','2','3','4','5','6'"
     );
   }
   
   
   
   public Collection<RequestBean> selectRequests(
       String orderBy,
       String order,
       int page,
       int itemsPerPage,
       IntWrapper records,
       String status
     ) {
      
       Connection connection = null;     
       PreparedStatement preparedStatement = null;
       ResultSet resultSet = null;
       
       try {         
         connection = dataSource.getConnection();               
         order = (order.equals("ASC") ? "ASC" : "DESC");         
         try {         
           RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
           switch (orderEnum) {
           case N:
             orderBy = "`request`.`id`";
             break;
           case regnumber:
             orderBy = "`request`.`registration_number`";
             break;
           case client:
             orderBy = "`user`.`lastname`";
             break;
           case organization:  
             orderBy = "`organization`.`shorttitle`";
             break;                    
           case status:
             orderBy = "`requeststatus`.`request_status`";
             break;
           case registrationdate:
              orderBy = "`request`.`registrationdate`";
              break;
           default:
             orderBy = "`request`.`id`";
             break;
           }
         } catch (Exception e) {
           orderBy = "`request`.`id`";
         }
         
         preparedStatement = connection.prepareStatement(
            "SELECT * FROM " +
            " `request`," + 
            " `user`," +
            " `organization`," +
            " `requeststatus`" +
            " WHERE `request`.`client_id` = `user`.`id`" +
            " AND `request`.`organization_id` = `organization`.`id`" +
            " AND `request`.`status_id` = `requeststatus`.`id`" +
            " AND `request`.`status_id` IN (" + status + ")" +
            " ORDER BY " + orderBy + " " + order
         );
                      
         resultSet = preparedStatement.executeQuery();                                   
                           
         Collection<RequestBean> result = 
           resultSetToCollection(
             resultSet,
             page,
             itemsPerPage,
             records
           );                                            
         
         
         
         return result;       
       } catch (Exception e) {
          handleException(e, "selectRequests");
          throw new EJBException(e);         
       } finally {
          DbUtils.closeQuietly(
             connection, 
             preparedStatement, 
             resultSet
          );
       }
     }
   
   
   public Collection<RequestBean> selectRequests(
      String orderBy,
      String order,
      String status
    ) {
     
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {         
        connection = dataSource.getConnection();               
        order = (order.equals("ASC") ? "ASC" : "DESC");         
        try {         
          RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
          switch (orderEnum) {
          case N:
            orderBy = "`request`.`id`";
            break;
          case regnumber:
            orderBy = "`request`.`registration_number`";
            break;
          case client:
            orderBy = "`user`.`lastname`";
            break;
          case organization:  
            orderBy = "`organization`.`shorttitle`";
            break;                    
          case status:
            orderBy = "`requeststatus`.`request_status`";
            break;
          case registrationdate:
             orderBy = "`request`.`registrationdate`";
             break;
          default:
            orderBy = "`request`.`id`";
            break;
          }
        } catch (Exception e) {
          orderBy = "`request`.`id`";
        }
        
        preparedStatement = connection.prepareStatement(
           "SELECT * FROM " +
           " `request`," + 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`status_id` IN (" + status + ")" +
           " ORDER BY " + orderBy + " " + order
        );
                     
        resultSet = preparedStatement.executeQuery();                                   
                          
        Collection<RequestBean> result = 
          resultSetToCollection(
            resultSet
          );                                                            
        
        return result;       
      } catch (Exception e) {
         handleException(e, "selectRequests");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
    }
   
      
   
   public Collection<RequestBean> selectRequestsByGroup(int groupId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`group_id` = ?" +
           " ORDER BY `requeststatus`.`request_status`"
       );
       
       preparedStatement.setInt(1, groupId);
       resultSet = preparedStatement.executeQuery();
       
       Collection<RequestBean> requestList = 
          resultSetToCollection(resultSet);
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequestsByGroup");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }      
   }
   
   
   
   public Collection<RequestBean> selectRequestsByLeadExecutor(int leadExecutorId) {
     return this.selectRequestsByLeadExecutor(leadExecutorId, "", "ASC");
   }
   
   
   
   public Collection<RequestBean> selectRequestsByLeadExecutor(
     int leadExecutorId,
     String orderBy,
     String order     
   ) {     
     return this.selectRequestsByLeadExecutor(
       leadExecutorId,
       orderBy,
       order,
       "'1','2','3','4','5','6'"
     );
   }
   
   
   
   public Collection<RequestBean> selectRequestsByLeadExecutor(
     int leadExecutorId,
     String orderBy,
     String order,
     String status
   ) {
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();
       
       order = (order.equals("DESC")) ? "DESC" : "ASC";
       try {
        RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
        switch (orderEnum) {
        case N:
          orderBy = "`request`.`id`";
          break;
        case client:
          orderBy = "`user`.`lastname`";
          break;
        case organization:  
          orderBy = "`organization`.`shorttitle`";
          break;
        case status:
          orderBy = "`requeststatus`.`request_status`";
          break;
        case registrationdate:
           orderBy = "`request`.`registrationdate`";
           break;
        default:
          orderBy = "`request`.`id`";
          break;
        }
      } catch (Exception e) {
        orderBy = "`request`.`id`";
      }
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`leadexecutor_id` = ?" +
           " AND `request`.`status_id` IN (" + status + ")" +
           " ORDER BY " + orderBy + " " + order
       );
       
       preparedStatement.setInt(1, leadExecutorId);
//       System.out.println(preparedStatement.toString());
       resultSet = preparedStatement.executeQuery();
       Collection<RequestBean> requestList = resultSetToCollection(resultSet);

       return requestList;
     } catch (Exception e) {
        handleException(e, "selectRequestsByLeadExecutor");
        throw new EJBException(e);
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }     
   }
   
   
   
   /**
    * Select Request By lead Executor with paging
    * @param leadExecutorId
    * @param orderBy
    * @param order
    * @param status
    * @param page
    * @param itemsPerPage
    * @param records
    * @return
    */
   public Collection<RequestBean> selectRequestsByLeadExecutor(
     int leadExecutorId,
     String orderBy,
     String order,
     String status,
     int page,
     int itemsPerPage,
     IntWrapper records
   ) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null; 
       
     try {
       connection = dataSource.getConnection();
       
       order = (order.equals("DESC")) ? "DESC" : "ASC";
       try {         
        RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
        switch (orderEnum) {
        case N:
          orderBy = "`request`.`id`";
          break;
        case client:
          orderBy = "`user`.`lastname`";
          break;
        case organization:  
          orderBy = "`organization`.`shorttitle`";
          break;                    
        case status:
          orderBy = "`requeststatus`.`request_status`";
          break;
        case registrationdate:
          orderBy = "`request`.`registrationdate`";
          break; 
        default:
          orderBy = "`request`.`id`";
          break;
        }
      } catch (Exception e) {
        orderBy = "`request`.`id`";
      }
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`leadexecutor_id` = ?" +
           " AND `request`.`status_id` IN (" + status + ")" +
           " ORDER BY " + orderBy + " " + order
       );
       
       preparedStatement.setInt(1, leadExecutorId);       
       
       //System.out.println(preparedStatement.toString());
       
       resultSet = preparedStatement.executeQuery();
       
       Collection<RequestBean> requestList = 
         resultSetToCollection(
           resultSet,
           page,
           itemsPerPage,
           records
         );
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequestsByLeadExecutor");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   
   /**
    * Select current active requests for lead
    * @param leadExecutorId
    * @return
    */
   public Collection<RequestBean> selectBurningRequestsByLeadExecutor(
     int leadExecutorId
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();       
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`leadexecutor_id` = ?" +
           " AND `request`.`status_id` IN ('3', '4')" +
           " AND ? BETWEEN `request`.`registrationdate` AND `request`.`approxenddate`" +
           " ORDER BY `request`.`id` DESC"
       );
       
       preparedStatement.setInt(1, leadExecutorId);
       preparedStatement.setDate(2, new java.sql.Date(new java.util.Date().getTime()));
              
       resultSet = preparedStatement.executeQuery();       
       Collection<RequestBean> requestList = resultSetToCollection(resultSet);
            
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectBurningRequestsByLeadExecutor");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   
   public Collection<RequestBean> selectRequestsByClient(
     int clientId
   ) {              
     return this.selectRequestsByClient(clientId, "", "ASC");     
   }
   
   
   
   public Collection<RequestBean> selectRequestsByClient(
     int clientId, 
     String orderBy, 
     String order
   ) {              
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();
       
       order = (order.equals("DESC")) ? "DESC" : "ASC";
       try {         
        RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
        switch (orderEnum) {
        case regnumber:
          orderBy = "`request`.`registration_number`";
          break;
        case registrationdate:  
          orderBy = "`request`.`registrationdate`";
          break;                    
        case status:
          orderBy = "`requeststatus`.`request_status`";
          break;
        default:
          orderBy = "`requeststatus`.`request_status`";
          break;
        }
      } catch (Exception e) {
        orderBy = "`requeststatus`.`request_status`";
      }
      
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`client_id` = ?" +
           " ORDER BY " + orderBy + " " + order 
       );
       
       preparedStatement.setInt(1, clientId);       
       
       resultSet = preparedStatement.executeQuery();       
       Collection<RequestBean> requestList = 
         resultSetToCollection(resultSet);
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequestsByClient");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }     
   }
   
   
   
   public Collection<RequestBean> selectRequestsByClient(
     int clientId, 
     String orderBy, 
     String order,
     int page,
     int itemsPerPage,
     IntWrapper records
   ) {              
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();
       
       order = (order.equals("DESC")) ? "DESC" : "ASC";
       try {         
        RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
        switch (orderEnum) {
        case N:
          orderBy = "`request`.`id`";
          break;
        case regnumber:
          orderBy = "`request`.`registration_number`";
          break;
        case registrationdate:  
          orderBy = "`request`.`registrationdate`";
          break;                    
        case status:
          orderBy = "`requeststatus`.`request_status`";
          break;
        default:
          orderBy = "`request`.`id`";
          break;
        }
      } catch (Exception e) {
        orderBy = "`request`.`id`";
      }
      
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`client_id` = ?" +
           " ORDER BY " + orderBy + " " + order 
       );
       
       preparedStatement.setInt(1, clientId);       
       
       resultSet = preparedStatement.executeQuery();
       
       Collection<RequestBean> requestList = 
         resultSetToCollection(
           resultSet,
           page,
           itemsPerPage,
           records
         );
              
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectRequestsByClient");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   
   public Collection<RequestBean> selectNewRequestsByLeadExecutor(
     int leadExecutorId
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {       
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM "+
           " `request`,"+ 
           " `user`," +
           " `organization`," +
           " `requeststatus`" +
           " WHERE `request`.`client_id` = `user`.`id`" +
           " AND `request`.`organization_id` = `organization`.`id`" +
           " AND `request`.`status_id` = `requeststatus`.`id`" +
           " AND `request`.`leadexecutor_id` = ?" +
           " AND `request`.`status_id` IN ('4', '5', '6')" +
           " ORDER BY `requeststatus`.`request_status`"
       );
       
       preparedStatement.setInt(1, leadExecutorId);
       resultSet = preparedStatement.executeQuery();
       
       Collection<RequestBean> requestList = resultSetToCollection(resultSet);
       
       return requestList;       
     } catch (Exception e){
        handleException(e, "selectNewRequestsByLeadExecutor");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }     
   }   
   
   
   
   public boolean addRequest(RequestBean requestBean, int userId) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      
      try {
         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "INSERT INTO `request`" + 
            " SET `approxenddate` = ?," + 
            " `comment` = ?," + 
            " `priority` = ?," + 
            " `room` = ?," + 
            " `status_id` = ?," + 
            " `clienttext` = ?," + 
            " `text` = ?," + 
            " `group_id` = ?," + 
            " `department_id` = ?," + 
            " `organization_id` = ?," + 
            " `client_id` = ?," + 
            " `requesttype_id` = ?," + 
            " `leadexecutor_id` = ?," + 
            " `registrationdate` = ?," + 
            " `registrator_id` = ?," + 
            " `registration_number` = ?," +
            " `name` = ?"
         );

         java.util.Date date = requestBean.getApproxEndDate();
         if (date != null)
            preparedStatement.setDate(1, new java.sql.Date(date.getTime()));
         else
            preparedStatement.setNull(1, Types.DATE);

         preparedStatement.setString(2, requestBean.getComment());
         preparedStatement.setInt(3, requestBean.getPriority());
         preparedStatement.setString(4, requestBean.getRoom());
         preparedStatement.setInt(5, requestBean.getStatusId());

         preparedStatement.setString(6, requestBean.getClientText());

         if (requestBean.getDispatcherText() != null) {
            preparedStatement.setString(7, requestBean.getDispatcherText());
         } else
            preparedStatement.setNull(7, Types.NULL);

         setId(preparedStatement, 8, requestBean.getGroupId());
         setId(preparedStatement, 9, requestBean.getDepartmentId());
         setId(preparedStatement, 10, requestBean.getOrganizationId());
         setId(preparedStatement, 11, requestBean.getClientId());
         setId(preparedStatement, 12, requestBean.getRequestTypeId());
         setId(preparedStatement, 13, requestBean.getLeadExecutorId());

         if (requestBean.getRegistrationDate() != null) {
            preparedStatement.setTimestamp(14, new java.sql.Timestamp(
               requestBean.getRegistrationDate().getTime()));
         } else
            preparedStatement.setNull(14, Types.TIMESTAMP);

         setId(preparedStatement, 15, requestBean.getRegistratorId());
         preparedStatement.setString(16, requestBean.getRegNumber());
         preparedStatement.setString(17, requestBean.getName());

         int result = preparedStatement.executeUpdate();

         ResultSet resultSet = preparedStatement.getGeneratedKeys();
         if (resultSet.next()) {
            requestBean.setId(resultSet.getInt(1));
            this.downInHistoryRequest(requestBean.getId(), userId);
         }

         return (result != 0);
      } catch (Exception e) {
         handleException(e, "addRequest");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement);
      }
   }
   
   
   
   public boolean updateRequest(RequestBean requestBean, int userId) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {               
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `request`" +
           " SET `approxenddate` = ?," +
           " `comment` = ?," +
           " `priority` = ?," +
           " `room` = ?," +
           " `status_id` = ?," +
           " `clienttext` = ?," +
           " `text` = ?," +
           " `group_id` = ?," +
           " `department_id` = ?," +
           " `organization_id` = ?," +
           " `client_id` = ?," +
           " `requesttype_id` = ?," +           
           " `leadexecutor_id` = ?," +
           " `registrationdate` = ?," +   
//           " `registrator_id` = ?," +
           " `registration_number` = ?" +
           " WHERE `id` = ?"
       );             
       
       java.util.Date date = requestBean.getApproxEndDate();
       if (date!=null)
         preparedStatement.setDate(
           1, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(1, Types.DATE);
       
       preparedStatement.setString(2, requestBean.getComment());       
       preparedStatement.setInt(3, requestBean.getPriority());       
       preparedStatement.setString(4, requestBean.getRoom());
       preparedStatement.setInt(5, requestBean.getStatusId());
       preparedStatement.setString(6, requestBean.getClientText());
       preparedStatement.setString(7, requestBean.getDispatcherText());       
       setId(preparedStatement, 8, requestBean.getGroupId());       
       setId(preparedStatement, 9, requestBean.getDepartmentId());
       setId(preparedStatement, 10, requestBean.getOrganizationId());
       setId(preparedStatement, 11, requestBean.getClientId());
       setId(preparedStatement, 12, requestBean.getRequestTypeId());
       setId(preparedStatement, 13, requestBean.getLeadExecutorId());              
       
       date = requestBean.getRegistrationDate();
       if (date!=null)
         preparedStatement.setTimestamp(
           14, new Timestamp(date.getTime())
         );
       else preparedStatement.setNull(14, Types.TIMESTAMP);
       
//       setId(preparedStatement, 15, requestBean.getRegistratorId());       
       preparedStatement.setString(15, requestBean.getRegNumber());                                                             
       preparedStatement.setInt(16, requestBean.getId());
       
       int result = preparedStatement.executeUpdate();
         
       if (result != 0)
         this.downInHistoryRequest(requestBean.getId(), userId);
       
       return (result != 0) ? true : false;
     } catch (Exception e) {
       handleException(e, "updateRequest");
       throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     } 
   }
   
   
   
   public boolean updateRequestByLeadExecutor(
     RequestBean requestBean, 
     int userId
   ) {
     
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      
     try {               
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `request`" +
           " SET `approxenddate` = ?," +
           " `comment` = ?," +
           " `priority` = ?," +           
           " `clienttext` = ?," +
           " `text` = ?," +
//           " `group_id` = ?," +
           " `requesttype_id` = ?," +           
           " `leadexecutor_id` = ?," +
           " `status_id` = ?," +
           " `name` = ?" +   
           " WHERE `id` = ?" +
           " AND `leadexecutor_id` = ?"
       );             
       
       java.util.Date date = requestBean.getApproxEndDate();
       if (date != null)
         preparedStatement.setDate(
           1, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(1, Types.DATE);       
       preparedStatement.setString(2, requestBean.getComment());
       preparedStatement.setInt(3, requestBean.getPriority());
       preparedStatement.setString(4, requestBean.getClientText());       
       preparedStatement.setString(5, requestBean.getDispatcherText());       
       preparedStatement.setInt(6, requestBean.getRequestTypeId());
       preparedStatement.setInt(7, requestBean.getLeadExecutorId());                     
       preparedStatement.setInt(8, requestBean.getStatusId());
       preparedStatement.setString(9, requestBean.getName());
       
       preparedStatement.setInt(10, requestBean.getId());
       preparedStatement.setInt(11, requestBean.getLeadExecutorId());
                            
       int result = preparedStatement.executeUpdate();
         
       preparedStatement = connection.prepareStatement(
          "UPDATE `task` SET" +
          " `approxenddate` = ?" +
          " WHERE `task`.`request_id` = ?" +
          " AND `task`.`approxenddate` > ?"
       );
              
       preparedStatement.setDate(1, new java.sql.Date(date.getTime()));
       preparedStatement.setInt(2, requestBean.getId());
       preparedStatement.setDate(3, new java.sql.Date(date.getTime()));              
//     TODO down in history task
       preparedStatement.executeUpdate();
       
       
       preparedStatement = connection.prepareStatement(
          "UPDATE `task` SET" +
          " `startdate` = ?" +
          " WHERE `task`.`request_id` = ?" +
          " AND `task`.`startdate` > ?"
       );
              
       preparedStatement.setDate(1, new java.sql.Date(date.getTime()));
       preparedStatement.setInt(2, requestBean.getId());
       preparedStatement.setDate(3, new java.sql.Date(date.getTime()));              
//     TODO down in history task
       preparedStatement.executeUpdate();
       
       if (result != 0)
         this.downInHistoryRequest(requestBean.getId(), userId, connection);              
       
       return (result != 0);
     } catch (Exception e) {
       handleException(e, "updateRequestByLeadExecutor");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   public Collection<RequestTypeBean> selectRequestTypes() {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       ArrayList<RequestTypeBean> requestTypesList = 
         new ArrayList<RequestTypeBean>();
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `requesttype`"           
       );
             
       resultSet = preparedStatement.executeQuery();
       
       while(resultSet.next()) {
         RequestTypeBean requestTypeBean = new RequestTypeBean();
         requestTypeBean.setId(resultSet.getInt("id"));
         requestTypeBean.setType(resultSet.getString("request_type"));
         requestTypesList.add(requestTypeBean);
       }
       
       return requestTypesList;       
     } catch (Exception e){
        handleException(e, "selectRequestTypes");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }    
   }
   
   
   
   public Collection<TaskStatusBean> selectTaskStatus() {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       ArrayList<TaskStatusBean> taskStatusList = 
         new ArrayList<TaskStatusBean>();
       
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `taskstatus`"           
       );
             
       resultSet = preparedStatement.executeQuery();
       
       while(resultSet.next()) {
         TaskStatusBean taskStatusBean = new TaskStatusBean();
         taskStatusBean.setId(resultSet.getInt("id"));
         taskStatusBean.setStatus(resultSet.getString("status_name"));
         taskStatusList.add(taskStatusBean);
       }
       
       return taskStatusList;       
     } catch (Exception e){
        handleException(e, "selectTaskStatus");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   
   public Collection<Integer> passivateTasks(
     int requestId,
     int userId
   ) {
     Collection<Integer> tasksId = new ArrayList<Integer>();
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
         "SELECT `id` FROM `task`" +                   
         " WHERE `request_id` = ?" + 
         " AND `status_id` = 1"
       );     
                    
       preparedStatement.setInt(1, requestId);
       
       ResultSet resultSet = preparedStatement.executeQuery();
       
       while(resultSet.next()) {
         int id = resultSet.getInt("id");
         this.setTaskStatus(id, 2, userId, connection);
         tasksId.add(id);
       }                    
       
       return tasksId;
     } catch (Exception e) {
       handleException(e, "passivateTasks");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   public Collection<Integer> activateTasks(int requestId, int userId) {
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;                 

      try {
         Collection<Integer> tasksId = new ArrayList<Integer>();
         
         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "SELECT `id` FROM `task`" + 
            " WHERE `request_id` = ?" + 
            " AND `status_id` = 2"
         );

         preparedStatement.setInt(1, requestId);

         resultSet = preparedStatement.executeQuery();

         while (resultSet.next()) {
            int id = resultSet.getInt("id");
            this.setTaskStatus(id, 1, userId, connection);
            tasksId.add(id);
         }

         return tasksId;
      } catch (Exception e) {
         handleException(e, "activateTasks");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }
   
   
   
   public boolean setRequestStatus(
     int requestId, 
     int statusId,
     int userId
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
         "UPDATE `request`" +
         " SET `request`.`status_id` = ?," +
         " `request`.`realEndDate` = ?" +
         " WHERE `request`.`id` = ?"
       );     
                    
       preparedStatement.setInt(1, statusId);
       if(statusId == 6) {
         preparedStatement.setTimestamp(
           2, 
           new Timestamp((new java.util.Date()).getTime())
         );    
       } else preparedStatement.setNull(2, Types.TIMESTAMP);
       preparedStatement.setInt(3, requestId);       
       
       int result = preparedStatement.executeUpdate();
       
       if (result != 0) {
         this.downInHistoryRequest(requestId, userId, connection);
       }             
       
       return (result!=0);
     } catch (Exception e) {
       handleException(e, "setRequestStatus");
       throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     } 
   }
   
   
   
   public boolean setTaskStatus(
     int taskId, 
     int statusId, 
     int userId
   ) {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
         "UPDATE `task`" +
         " SET `task`.`status_id` = ?," +
         " `task`.`enddate` = ?" +
         " WHERE `task`.`id` = ?"         
       );     
                    
       preparedStatement.setInt(1, statusId);
       if(statusId == 3) {
         preparedStatement.setTimestamp(
           2, 
           new Timestamp((new java.util.Date()).getTime())
         );    
       } else preparedStatement.setNull(2, Types.TIMESTAMP);
       preparedStatement.setInt(3, taskId);      
       
       int result = preparedStatement.executeUpdate();
       if (result != 0) {
         this.downInHistoryTask(taskId, userId);
       }      
       
       return (result == 0) ? false : true;
     } catch (Exception e) {
       handleException(e, "setTaskStatus");
       throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }
   }
   
   
   
   public boolean setTaskStatus(
     int taskId, 
     int statusId, 
     int userId,
     Connection connection
   ) {         
     PreparedStatement preparedStatement = null;
     try {                           
       preparedStatement = connection.prepareStatement(
         "UPDATE `task`" +
         " SET `status_id` = ?" +           
         " WHERE `id` = ?"         
       );     
                    
       preparedStatement.setInt(1, statusId);
       preparedStatement.setInt(2, taskId);
       
       
       int result = preparedStatement.executeUpdate();
       if (result != 0) {
         this.downInHistoryTask(taskId, userId);
       }              
       
       return (result != 0);
     } catch (Exception e) {
       handleException(e, "setTaskStatus");
       throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(preparedStatement);
     } 
   }
   
   
   
   public void commitRequestByDispatcher(RequestBean requestBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
        connection = dataSource.getConnection();       
        preparedStatement = connection.prepareStatement(
           "UPDATE `request`" +
           " SET `text` = ?," +
           " `clienttext` = ?," +
           " `client_id` = ?," +
           " `organization_id` = ?," +
           " `department_id` = ?," +
           " `group_id` = ?," +
           " `requesttype_id` = ?," +
           " `room` = ?," +
           " `status_id` = '3'" +
           " WHERE `id` = ?"
        );             
       
         preparedStatement.setString(1, requestBean.getDispatcherText());
         preparedStatement.setString(2, requestBean.getClientText());
         preparedStatement.setInt(3, requestBean.getClientId());
         preparedStatement.setInt(4, requestBean.getOrganizationId());
         preparedStatement.setInt(5, requestBean.getDepartmentId());
         preparedStatement.setInt(6, requestBean.getGroupId());
         preparedStatement.setInt(7, requestBean.getRequestTypeId());
         preparedStatement.setString(8, requestBean.getRoom());
         preparedStatement.setInt(9, requestBean.getId());
         preparedStatement.executeUpdate();

      } catch (Exception e) {
         handleException(e, "commitRequestByDispatcher");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement);
      }
   }
   
   
   public RequestBean selectRequestDetails(int requestId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       RequestBean requestBean = new RequestBean();
       connection = dataSource.getConnection();       
       
       preparedStatement = connection.prepareStatement(         
         "SELECT * FROM `request`" +
         " LEFT JOIN `requeststatus` ON `request`.`status_id` = `requeststatus`.`id`" +
         " LEFT JOIN `organization` ON `organization`.`id` = `request`.`organization_id`" +
         " LEFT JOIN `department` ON `request`.`department_id` = `department`.`id`" +
         " LEFT JOIN `requesttype` ON `request`.`requesttype_id` = `requesttype`.`id`" +
         " LEFT JOIN `user` as `client` ON `request`.`client_id` = `client`.`id`" +
         " LEFT JOIN `client_settings` ON `client`.`id` = `client_settings`.`user_id`" +
         " LEFT JOIN `priority` ON `priority`.`id` = `request`.`priority`" +
         " LEFT JOIN `group` ON `group`.`id` = `request`.`group_id`" +
         " LEFT JOIN `user` as `leadexecutor` ON `leadexecutor`.`id` = `request`.`leadexecutor_id`" + 
         " WHERE `request`.`id` = ?"          
       );
       
       preparedStatement.setInt(1, requestId);             
       resultSet = preparedStatement.executeQuery();
       
       if(resultSet.next()) {
         OrganizationBean organizationBean = 
            new OrganizationBean(resultSet);                  
         requestBean.setOrganization(organizationBean);
         
         DepartmentBean departmentBean = 
            new DepartmentBean(resultSet);         
         requestBean.setDepartment(departmentBean);
         
         RequestStatusBean requestStatusBean = 
            new RequestStatusBean(resultSet);
         requestBean.setRequestStatus(requestStatusBean);
         
         RequestTypeBean requestTypeBean = 
            new RequestTypeBean(resultSet);                  
         requestBean.setRequestType(requestTypeBean);
         
         ClientBean clientBean = new ClientBean(resultSet, "client");         
         requestBean.setClient(clientBean);
         
         GroupBean groupBean = 
            new GroupBean(resultSet);         
         requestBean.setGroup(groupBean);
         
         UserBean employeeBean = 
            new UserBean(resultSet, "leadexecutor");         
         requestBean.setLeadExecutor(employeeBean);
         
         RequestPriorityBean priorityBean = 
            new RequestPriorityBean(resultSet);        
         requestBean.setPriorityBean(priorityBean);
         
         requestBean.fillFromResultSet(resultSet);
       }
       
       return requestBean;       
     } catch (Exception e){
        handleException(e, "selectRequestDetails");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   
   public TaskBean selectTask(int taskId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {       
       connection = dataSource.getConnection();                   
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM" +
           " `task`," +
           " `user`," +
           " `employee_settings`," +
           " `taskstatus`," +
           " `priority`" +
           " WHERE `task`.`id` = ?" +
           " AND `user`.`id` = `task`.`employee_id`" +
           " AND `employee_settings`.`user_id` = `user`.`id`" +
           " AND `task`.`status_id` = `taskstatus`.`id`" +
           " AND `task`.`priority` = `priority`.`id`"
       );
       
       preparedStatement.setInt(1, taskId);
       resultSet = preparedStatement.executeQuery();
              
       TaskBean taskBean = new TaskBean();
              
       if(resultSet.next()) {         
         EmployeeBean executor = new EmployeeBean();
         TaskStatusBean taskStatusBean = new TaskStatusBean();
         
         taskStatusBean.fillFromResultSet(resultSet);         
         taskBean.setStatus(taskStatusBean);
         
         executor.setEmail(resultSet.getString("user.email"));
         executor.setFirstName(resultSet.getString("user.firstname"));
         executor.setLastName(resultSet.getString("user.lastname"));
         executor.setMiddleName(resultSet.getString("user.middlename"));
         taskBean.setExecutor(executor);
         
         RequestPriorityBean priorityBean = 
            new RequestPriorityBean(resultSet);
         
         taskBean.setPriorityBean(priorityBean);
         
         taskBean.fillFromResultSet(resultSet);
       }              
       
       return taskBean;       
     } catch (Exception e){
        handleException(e, "selectTask");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(
           connection, 
           preparedStatement, 
           resultSet
        );
     }
   }
   
   
   
   public boolean addTask(TaskBean taskBean) {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "INSERT INTO `task`" +
           " SET `request_id` = ?," +
           " `employee_id` = ?," +
           " `status_id` = ?," +
           " `text` = ?," +
           " `executorreport` = ?," +
           " `startdate` = ?," +
           " `approxenddate` = ?," +
           " `priority` = ?," +
           " `name` = ?"
       );             
       
       setId(preparedStatement, 1, taskBean.getRequestId());
       setId(preparedStatement, 2, taskBean.getEmployeeId());
       setId(preparedStatement, 3, taskBean.getStatusId());
       preparedStatement.setString(4, taskBean.getText());
       preparedStatement.setString(5, taskBean.getExecutorReport());
       
       java.util.Date date = taskBean.getStartDate();
       if (date!=null)
         preparedStatement.setDate(
           6, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(6, Types.DATE);
       
       date = taskBean.getApproxEndDate();
       if (date!=null)
         preparedStatement.setDate(
           7, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(7, Types.DATE);
       
       preparedStatement.setInt(8, taskBean.getPriority());
       preparedStatement.setString(9, taskBean.getName());
       
       int result = preparedStatement.executeUpdate();
       
       ResultSet resultSet = preparedStatement.getGeneratedKeys();
       if (resultSet.next()) {
          taskBean.setId(resultSet.getInt(1));
          //this.downInHistoryRequest(requestBean.getId(), userId);
       }
       
       return (result != 0);
     } catch (Exception e) {
       handleException(e, "addTask");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   public boolean updateTask(TaskBean taskBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `task`" +
           " SET `request_id` = ?," +
           " `employee_id` = ?," +
           " `status_id` = ?," +
           " `text` = ?," +
           " `executorreport` = ?," +
           " `startdate` = ?," +
           " `approxenddate` = ?," +
           " `priority` = ?" +
           " WHERE `id` = ?"
       );             
       
       setId(preparedStatement, 1, taskBean.getRequestId());
       setId(preparedStatement, 2, taskBean.getEmployeeId());
       setId(preparedStatement, 3, taskBean.getStatusId());
       preparedStatement.setString(4, taskBean.getText());
       preparedStatement.setString(5, taskBean.getExecutorReport());
       
       java.util.Date date = taskBean.getStartDate();
       if (date!=null)
         preparedStatement.setDate(
           6, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(6, Types.DATE);
       
       date = taskBean.getApproxEndDate();
       if (date!=null)
         preparedStatement.setDate(
           7, new java.sql.Date(date.getTime())
         );
       else preparedStatement.setNull(7, Types.DATE);
       
       preparedStatement.setInt(8, taskBean.getPriority());
       preparedStatement.setInt(9, taskBean.getId());
       
       int result = preparedStatement.executeUpdate();
       
       return (result != 0);
     } catch (Exception e) {
       handleException(e, "updateTask");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(
          connection, 
          preparedStatement
       );
    }
   }
   
   
   
   public Collection<TaskBean> selectTasksByRequest(
     int requestId
   ) {
     return selectTasksByRequest(requestId, "", "ASC");
   }
   
   
   
   public Collection<TaskBean> selectTasksByRequest(
     int requestId,
     String orderBy,
     String order
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {       
       connection = dataSource.getConnection();       
       ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();
       
       order = (order.equals("ASC") ? "ASC" : "DESC");
       
       try {         
         TaskOrderByEnum orderEnum = 
           TaskOrderByEnum.valueOf(orderBy);
         switch (orderEnum) {
         case N:
           orderBy = "`task`.`id`";
           break;
         case executor:
           orderBy = "`user`.`lastname`";
           break;
         case startdate:  
           orderBy = "`task`.`startdate`";
           break;
         case enddate:  
           orderBy = "`task`.`enddate`";
           break;
         case approxenddate:  
           orderBy = "`task`.`approxenddate`";
           break;  
         case status:
           orderBy = "`taskstatus`.`status_name`";
           break;
         case priority:
            orderBy = "`task`.`priority`";
            break;
         default:
           orderBy = "`task`.`id`";
           break;
         }
       } catch (Exception e) {
         orderBy = "`task`.`id`";
       }
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM" +
           " `task`," +
           " `user`," +
           " `employee_settings`," +
           " `taskstatus`" +
           " WHERE `task`.`request_id` = ?" +
           " AND `user`.`id` = `task`.`employee_id`" +
           " AND `employee_settings`.`user_id` = `user`.`id`" +
           " AND `task`.`status_id` = `taskstatus`.`id`" +
           " ORDER BY " + orderBy + " "+ order
       );
       
       preparedStatement.setInt(1, requestId);
       resultSet = preparedStatement.executeQuery();
       
       EmployeeBean executor = null;
       TaskBean taskBean = null;
       TaskStatusBean taskStatusBean = null;
       
       while(resultSet.next()) {
         executor = new EmployeeBean();
         taskBean = new TaskBean();
         taskStatusBean = new TaskStatusBean(resultSet);
         
         taskBean.setStatus(taskStatusBean);         
         executor.fillFromResultSet(resultSet);
         taskBean.setExecutor(executor);
         
         taskBean.fillFromResultSet(resultSet);
         
         tasksList.add(taskBean);
       }             
       
       return tasksList;       
     } catch (Exception e){
        handleException(e, "selectTasksByRequest");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
   }
   
   
   public Collection<TaskBean> selectTasksByExecutor(
     int executorId,
     String orderBy, 
     String order,
     int page,
     int itemsPerPage,
     IntWrapper records
  ) {      
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {       
        connection = dataSource.getConnection();
        
        order = (order.equals("DESC")) ? "DESC" : "ASC";
        try {         
         TaskOrderByEnum taskOrderByEnum = TaskOrderByEnum.valueOf(orderBy);
         switch (taskOrderByEnum) {
         case N:
           orderBy = "`task`.`id`";
           break;
         case startdate:
           orderBy = "`task`.`startdate`";
           break;
         case enddate:  
           orderBy = "`task`.`enddate`";
           break;
         case approxenddate:  
           orderBy = "`task`.`approxenddate`";
           break;  
         case status:
           orderBy = "`taskstatus`.`status_name`";
           break;
         case priority:
             orderBy = "`task`.`priority`";
             break;
         default:
           orderBy = "`task`.`id`";
           break;
         }
       } catch (Exception e) {
         orderBy = "`taskstatus`.`status_name`";
       }
        
       //ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();
        
        preparedStatement = connection.prepareStatement(
            "SELECT * FROM" +
            " `task`," +
            " `taskstatus`" +
            " WHERE `task`.`employee_id` = ?" +
            " AND `task`.`status_id` = `taskstatus`.`id`" +
            " ORDER BY " + orderBy + " " + order
        );
        
        preparedStatement.setInt(1, executorId);
        resultSet = preparedStatement.executeQuery();
        
        Collection<TaskBean> tasksList = 
           this.resultSetToTaskCollection(
              resultSet, 
              page, 
              itemsPerPage, 
              records
           );
                         
        return tasksList;       
      } catch (Exception e){
         handleException(e, "selectTasksByExecutor");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
      
      
  }
   
      
   public Collection<TaskBean> selectTasksByExecutor(int executorId) {
     return this.selectTasksByExecutor(executorId, "", "ASC");
   }
   
   
   
   public Collection<TaskBean> selectTasksByExecutor(
     int executorId,
     String orderBy,
     String order
   ) {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {       
       connection = dataSource.getConnection();
       
       order = (order.equals("DESC")) ? "DESC" : "ASC";
       try {         
        TaskOrderByEnum taskOrderByEnum = TaskOrderByEnum.valueOf(orderBy);
        switch (taskOrderByEnum) {
        case N:
          orderBy = "`task`.`id`";
          break;
        case startdate:
          orderBy = "`task`.`startdate`";
          break;
        case enddate:  
          orderBy = "`task`.`enddate`";
          break;
        case approxenddate:  
          orderBy = "`task`.`approxenddate`";
          break;  
        case status:
          orderBy = "`taskstatus`.`status_name`";
          break;
        case priority:
            orderBy = "`task`.`priority`";
            break;
        default:
          orderBy = "`task`.`id`";
          break;
        }
      } catch (Exception e) {
        orderBy = "`taskstatus`.`status_name`";
      }
       
       ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM" +
           " `task`," +
           " `user`," +
           " `employee_settings`," +
           " `taskstatus`" +
           " WHERE `task`.`employee_id` = ?" +
           " AND `user`.`id` = `task`.`employee_id`" +
           " AND `employee_settings`.`user_id` = `user`.`id`" +
           " AND `task`.`status_id` = `taskstatus`.`id`" +
           " ORDER BY " + orderBy + " " + order
       );
       
       preparedStatement.setInt(1, executorId);
       resultSet = preparedStatement.executeQuery();
       
       EmployeeBean executor = null;
       TaskBean taskBean = null;
       TaskStatusBean taskStatusBean = null;
       
       java.util.Date now = new java.util.Date();
       
       while(resultSet.next()) {
         int color = 0; // 0 - no color, 1 - current task, 2 - expire task
          
         executor = new EmployeeBean(resultSet);
         taskBean = new TaskBean(resultSet);
         taskStatusBean = new TaskStatusBean(resultSet);
         
         taskBean.setStatus(taskStatusBean);         
         taskBean.setExecutor(executor);                  
         
         if(taskBean.getStatus().getId() == 1 || 
            taskBean.getStatus().getId() == 2) {
            if(now.after(taskBean.getStartDate()) && 
               now.before(taskBean.getApproxEndDate())) {
               color = 1;
            } else if(now.after(taskBean.getApproxEndDate())) {
               color = 2;
            }
         }
         taskBean.setColor(color);
         
         tasksList.add(taskBean);
       }              
       
       return tasksList;       
     } catch (Exception e){
        handleException(e, "selectTasksByExecutor");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(
           connection, 
           preparedStatement, 
           resultSet
        );
     }
   }
   
   
   
   public boolean setRequestReport(int requestId, String report) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `request`" +
           " SET `leadexecutorreport` = ?" +           
           " WHERE `id` = ?"
       );             
       
       preparedStatement.setString(1, report);
       preparedStatement.setInt(2, requestId);
       
       int result = preparedStatement.executeUpdate();
       
       return (result != 0) ? true : false;
     } catch (Exception e) {
       handleException(e, "setRequestReport");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   public boolean setTaskReport(int taskId, String report) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `task`" +
           " SET `executorreport` = ?" +           
           " WHERE `id` = ?"
       );             
       
       preparedStatement.setString(1, report);
       preparedStatement.setInt(2, taskId);
       
       int result = preparedStatement.executeUpdate();
       
       return (result != 0) ? true : false;
     } catch (Exception e) {
       handleException(e, "setTaskReport");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   private void downInHistoryRequest(int requestId, int userId) {
      Connection connection = null;      
      try {
         connection = dataSource.getConnection();
         this.downInHistoryRequest(requestId, userId, connection);
      } catch (Exception e) {
         handleException(e, "downInHistoryRequest");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection);
      }
   }
   
   
   
   private void downInHistoryRequest(
      int requestId, 
      int userId, 
      Connection connection
   ) {         
     PreparedStatement preparedStatement = null;
     try {                           
       preparedStatement = connection.prepareStatement(                    
         "INSERT INTO `requesthistory` " +
         "(" +    
         " `requesthistory`.`name`," +
         " `requesthistory`.`realEndDate`," +
         " `requesthistory`.`approxEndDate`," +
         " `requesthistory`.`comment`," +
         " `requesthistory`.`priority`," +
         " `requesthistory`.`room`," +
         " `requesthistory`.`status_id`," +
         " `requesthistory`.`clientText`," +
         " `requesthistory`.`text`," +
         " `requesthistory`.`group_id`," +
         " `requesthistory`.`department_id`," +
         " `requesthistory`.`organization_id`," +
         " `requesthistory`.`client_id`," +
         " `requesthistory`.`leadexecutorreport`," +
         " `requesthistory`.`requesttype_id`," +
         " `requesthistory`.`leadexecutor_id`," +
         " `requesthistory`.`registrationdate`," +
         " `requesthistory`.`registrator_id`," +
         " `requesthistory`.`registration_number`," +
         " `requesthistory`.`change_author`," +
         " `requesthistory`.`change_date`," +
         " `requesthistory`.`srcrequest_id`" +
         ")" +
         " SELECT" +
         " `request`.`name`," +
         " `request`.`realEndDate`," +
         " `request`.`approxEndDate`," +
         " `request`.`comment`," +
         " `request`.`priority`," +
         " `request`.`room`," +
         " `request`.`status_id`," +
         " `request`.`clientText`," +
         " `request`.`text`," +
         " `request`.`group_id`," +
         " `request`.`department_id`," +
         " `request`.`organization_id`," +
         " `request`.`client_id`," +
         " `request`.`leadexecutorreport`," +
         " `request`.`requesttype_id`," +
         " `request`.`leadexecutor_id`," +
         " `request`.`registrationdate`," +
         " `request`.`registrator_id`," +
         " `request`.`registration_number`," +
         " ?," + //author
         " ?," + //date
         " `request`.`id`" +
         " FROM `request`" +
         " WHERE `request`.`id` = ?"
       );                   
       
       preparedStatement.setInt(1, userId);
       preparedStatement.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
       preparedStatement.setInt(3, requestId);       
       
       preparedStatement.executeUpdate();
              
     } catch (Exception e) {
       handleException(e, "downInHistoryRequest");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(preparedStatement);
    }
   }   
   
   
   
   private void downInHistoryTask(int taskId, int userId) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {              
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "INSERT INTO `taskhistory` " +
         "(" +
         " `taskhistory`.`name`," +
         " `taskhistory`.`request_id`," +
         " `taskhistory`.`employee_id`," +
         " `taskhistory`.`status_id`," +
         " `taskhistory`.`text`," +
         " `taskhistory`.`executorreport`," +
         " `taskhistory`.`startdate`," +
         " `taskhistory`.`enddate`," +                               
         " `taskhistory`.`change_author`," +
         " `taskhistory`.`change_date`," +
         " `taskhistory`.`srctask`," +
         " `taskhistory`.`priority`," +
         " `taskhistory`.`approxEndDate`" +
         ")" +
         " SELECT" +
         " `task`.`name`," +
         " `task`.`request_id`," +
         " `task`.`employee_id`," +
         " `task`.`status_id`," +
         " `task`.`text`," +
         " `task`.`executorreport`," +
         " `task`.`startdate`," +
         " `task`.`enddate`," +
         " ?," + //author
         " ?," + //date
         " `task`.`id`," +
         " `task`.`priority`," +
         " `task`.`approxEndDate`" +
         " FROM `task`" +
         " WHERE `task`.`id` = ?"
       );                   
       
       preparedStatement.setInt(1, userId);
       preparedStatement.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
       preparedStatement.setInt(3, taskId);      
       
       preparedStatement.executeUpdate();
             
     } catch (Exception e) {
       handleException(e, "downInHistoryTask");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }
   }
   
   
   
   public Collection<RequestHistoryBean> getRequestHistory(int requestId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "SELECT * FROM" +
         " `requesthistory`," +
         " `user` as `changeauthor`," +
         " `requeststatus`" +
         " WHERE `requesthistory`.`srcrequest_id` = ?" +
         " AND `changeauthor`.`id` = `requesthistory`.`change_author`" +
         " AND `requesthistory`.`status_id` = `requeststatus`.`id`"
       );                   
       
       preparedStatement.setInt(1, requestId);              
       resultSet = preparedStatement.executeQuery();
       
       ArrayList<RequestHistoryBean> requestHistoryList = 
          new ArrayList<RequestHistoryBean>();
       
       while (resultSet.next()) {
         RequestHistoryBean requestHistoryBean = new RequestHistoryBean();                  
         
         EmployeeBean employeeBean = new EmployeeBean();
         employeeBean.setFirstName(resultSet.getString("changeauthor.firstname"));
         employeeBean.setLastName(resultSet.getString("changeauthor.lastname"));
         employeeBean.setMiddleName(resultSet.getString("changeauthor.middlename"));
         employeeBean.setEmail(resultSet.getString("changeauthor.email"));
         requestHistoryBean.setChangeAuthor(employeeBean);
         
         RequestStatusBean requestStatusBean = new RequestStatusBean();
         requestStatusBean.setId(resultSet.getInt("requeststatus.id"));
         requestStatusBean.setStatus(resultSet.getString("requeststatus.request_status"));
         requestHistoryBean.setRequestStatus(requestStatusBean);
         
         requestHistoryBean.setChangeDate((java.util.Date)resultSet.getTimestamp("requesthistory.change_date"));         
         requestHistoryList.add(requestHistoryBean);
       }
                
       return requestHistoryList;
     } catch (Exception e) {
       handleException(e, "getRequestHistory");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   /**
    * Select request status list
    * @return
    */
   public Collection<RequestStatusBean> selectRequestStatuses() {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "SELECT * FROM `requeststatus`"        
       );                   
                           
       resultSet = preparedStatement.executeQuery();
       
       ArrayList<RequestStatusBean> requestStatusList = 
         new ArrayList<RequestStatusBean>();
       
       while (resultSet.next()) {                                                   
         RequestStatusBean requestStatusBean = new RequestStatusBean();
         requestStatusBean.setId(resultSet.getInt("requeststatus.id"));
         requestStatusBean.setStatus(resultSet.getString("requeststatus.request_status"));
         
         requestStatusList.add(requestStatusBean);
       }
                       
       return requestStatusList;
     } catch (Exception e) {
       handleException(e, "selectRequestStatuses");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   } 
   
   
   
   /**
    * Select request status list
    * @return
    */
   public RequestStatusBean selectRequestStatus(int requestId) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     RequestStatusBean requestStatusBean = null;
     
     try {
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "SELECT * FROM `requeststatus`, `request`" +
         " WHERE `request`.`status_id` = `requeststatus`.`id`" +
         " AND `request`.`id` = ?"
       );                   
                           
       preparedStatement.setInt(1, requestId);
       
       resultSet = preparedStatement.executeQuery();             
              
       if (resultSet.next()) {                                                   
         requestStatusBean = new RequestStatusBean();
         requestStatusBean.setId(resultSet.getInt("requeststatus.id"));
         requestStatusBean.setStatus(resultSet.getString("requeststatus.request_status"));                  
       }
                       
       return requestStatusBean;
     } catch (Exception e) {
       handleException(e, "selectRequestStatus");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   public boolean isAllTasksCompleated(int requestId) {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "SELECT * FROM `task`" +
         " WHERE `request_id` = ?" +
         " AND `status_id` IN (1, 2)"
       );                   
                           
       preparedStatement.setInt(1, requestId);
       resultSet = preparedStatement.executeQuery();
       boolean result = !resultSet.next();                 
       
       return result;       
     } catch (Exception e) {
       handleException(e, "isAllTasksCompleated");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   public Collection<RequestPriorityBean> selectPriorityList() {
     
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {       
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "SELECT * FROM `priority`"         
       );                   
                                  
       resultSet = preparedStatement.executeQuery();
       ArrayList<RequestPriorityBean> priorityList =
         new ArrayList<RequestPriorityBean>();
       
       while (resultSet.next()) {
         RequestPriorityBean priorityBean = 
           new RequestPriorityBean();
         priorityBean.setId(resultSet.getInt("id"));
         priorityBean.setValue(resultSet.getString("value"));
         priorityList.add(priorityBean);
       }         
       
       return priorityList;       
     } catch (Exception e) {
       handleException(e, "selectPriorityList");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   public boolean cancelRequestByDispatcher(
     RequestBean requestBean,
     int dispatcherId
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {       
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(                    
         "UPDATE `request`" +
         " SET `status_id` = 2," +
         " `comment` = ?" +
         " WHERE `id` = ?"         
       );                   
                      
       preparedStatement.setString(1, requestBean.getComment());
       preparedStatement.setInt(2, requestBean.getId());
       
       int result = preparedStatement.executeUpdate();
       
       this.downInHistoryRequest(
         requestBean.getId(), 
         dispatcherId
       );         
       
       return (result!=0);                 
     } catch (Exception e) {
       handleException(e, "cancelRequestByDispatcher");
       throw new EJBException(e);         
    }  finally {
       DbUtils.closeQuietly(connection, preparedStatement);
    }   
   }
   
   
   
   public ReportBean selectReport(java.util.Date startDate, java.util.Date endDate) {
     Connection connection = null;
     ResultSet resultSet = null;
     PreparedStatement preparedStatement1 = null;
     PreparedStatement preparedStatement2 = null;
     PreparedStatement preparedStatement3 = null;
     
     try {       
       ReportBean reportBean = new ReportBean();
       connection = dataSource.getConnection();       
       
       preparedStatement1 = connection.prepareStatement(                    
         "SELECT COUNT(*) as `totalAdded`"+
         " FROM `request`" +
         " WHERE `request`.`registrationdate` BETWEEN ? AND ?"         
       );
       
       preparedStatement2 = connection.prepareStatement(                    
         "SELECT COUNT(*) as `totalCompleated`"+
         " FROM `request`" +
         " WHERE `request`.`realenddate` BETWEEN ? AND ?"         
       );
       
       preparedStatement3 = connection.prepareStatement(                    
         "SELECT COUNT(*) as `totalTimeExpire`"+
         " FROM `request`" +
         " WHERE `request`.`realenddate` BETWEEN ? AND ?" +
         " AND `request`.`realenddate` > `request`.`approxenddate`"
       );
                                               
       preparedStatement1.setTimestamp(1, new Timestamp(startDate.getTime()));
       preparedStatement1.setTimestamp(2, new Timestamp(endDate.getTime()));
       
       preparedStatement2.setTimestamp(1, new Timestamp(startDate.getTime()));
       preparedStatement2.setTimestamp(2, new Timestamp(endDate.getTime()));
       
       preparedStatement3.setTimestamp(1, new Timestamp(startDate.getTime()));
       preparedStatement3.setTimestamp(2, new Timestamp(endDate.getTime()));
              
       resultSet = preparedStatement1.executeQuery();
       if (resultSet.next()) {
         reportBean.setTotalAdded(resultSet.getInt("totalAdded"));
       }
       
       resultSet = preparedStatement2.executeQuery();
       if (resultSet.next()) {
         reportBean.setTotalCompleated(resultSet.getInt("totalCompleated"));
       }
       
       resultSet = preparedStatement3.executeQuery();
       if (resultSet.next()) {
         reportBean.setTotalTimeExpire(resultSet.getInt("totalTimeExpire"));
       }
                
       preparedStatement1.close();
       preparedStatement2.close();
       preparedStatement3.close();
       connection.close();
       
       return reportBean;           
     } catch (Exception e) {
       handleException(e, "selectReport");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(resultSet);
       DbUtils.closeQuietly(preparedStatement1);
       DbUtils.closeQuietly(preparedStatement2);
       DbUtils.closeQuietly(preparedStatement3);       
       DbUtils.closeQuietly(connection);
    }  
   }
   
   
   
   /*
    * Search requests by client
    */   
   public Collection<RequestBean> searchRequestsByClient(
      RequestBean patternBean,
      String orderBy,
      String order,
      int page,
      int itemsPerPage,
      IntWrapper records,
      int clientId
   ) {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
      
      try {
                            
        connection = dataSource.getConnection();               
        order = (order.equals("ASC") ? "ASC" : "DESC");         
        try {         
          RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
          switch (orderEnum) {
          case N:
            orderBy = "`request`.`id`";
            break;
          case regnumber:
            orderBy = "`request`.`registration_number`";
            break;
          case client:
            orderBy = "`user`.`lastname`";
            break;
          case organization:  
            orderBy = "`organization`.`shorttitle`";
            break;                    
          case status:
            orderBy = "`requeststatus`.`request_status`";
            break;
          default:
            orderBy = "`request`.`id`";
            break;
          }
        } catch (Exception e) {
          orderBy = "`request`.`id`";
        }
        
        preparedStatement = connection.prepareStatement(
            "SELECT * FROM "+
            " `request`,"+ 
            " `user`," +
            " `organization`," +
            " `requeststatus`" +
            " WHERE `request`.`client_id` = `user`.`id`" +
            " AND `user`.`id` = ?" +
            " AND `request`.`organization_id` = `organization`.`id`" +
            " AND `request`.`status_id` = `requeststatus`.`id`" +
            " AND (" +
            "  `request`.`clientText` LIKE ?" +
            "  OR `request`.`text` LIKE ?" +
            " )" +
            " AND `requeststatus`.`id` = ?" +
            " AND `request`.`id` LIKE ?" +
            " AND `request`.`registration_number` LIKE ?" +
            " ORDER BY " + orderBy + " " + order
        );
        
        preparedStatement.setInt(1, clientId);
        preparedStatement.setString(2, "%"+patternBean.getClientText()+"%");
        preparedStatement.setString(3, "%"+patternBean.getClientText()+"%");
        preparedStatement.setInt(4, patternBean.getRequestStatus().getId());
        preparedStatement.setString(5, "%"+(patternBean.getId() == 0 ? "" : patternBean.getId())+"%");
        preparedStatement.setString(6, "%"+patternBean.getRegNumber()+"%");
        
        resultSet = preparedStatement.executeQuery();                                   
                         
        Collection<RequestBean> result = 
          resultSetToCollection(
            resultSet,
            page,
            itemsPerPage,
            records
          );                                            
                       
        return result;       
      } catch (Exception e){
         handleException(e, "searchRequestsByClient");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
   
   
   /*
    * Search requests by client
    */   
   public Collection<TaskBean> searchTasksByExecutor(
      TaskBean patternBean,
      String orderBy,
      String order,
      int page,
      int itemsPerPage,
      IntWrapper records
   ) {
      
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
      
      try {                           
        connection = dataSource.getConnection();
        
        order = (order.equals("DESC")) ? "DESC" : "ASC";
        try {         
         TaskOrderByEnum taskOrderByEnum = TaskOrderByEnum.valueOf(orderBy);
         switch (taskOrderByEnum) {
         case N:
           orderBy = "`task`.`id`";
           break;
         case startdate:
           orderBy = "`task`.`startdate`";
           break;
         case enddate:  
           orderBy = "`task`.`enddate`";
           break;
         case approxenddate:  
           orderBy = "`task`.`approxenddate`";
           break;  
         case status:
           orderBy = "`taskstatus`.`status_name`";
           break;
         case priority:
             orderBy = "`task`.`priority`";
             break;
         default:
           orderBy = "`task`.`id`";
           break;
         }
       } catch (Exception e) {
         orderBy = "`taskstatus`.`status_name`";
       }
        
       //ArrayList<TaskBean> tasksList = new ArrayList<TaskBean>();
        
        preparedStatement = connection.prepareStatement(
            "SELECT * FROM" +
            " `task`," +
            " `taskstatus`" +
            " WHERE `task`.`employee_id` = ?" +
            " AND `task`.`status_id` = `taskstatus`.`id`" +
            " AND `taskstatus`.`id` = ?" +
            " AND `task`.`id` LIKE ?" +
            " AND `task`.`text` LIKE ?" + 
            " AND DATE(`task`.`startdate`) BETWEEN ? AND ?" +
            " AND " +
            " ( " +
            "   ((DATE(`task`.`enddate`) BETWEEN ? AND ?) OR (`task`.`enddate` IS NULL))" +
            "   OR " +
            "   ((DATE(`task`.`approxEndDate`) BETWEEN ? AND ?) OR (`task`.`approxEndDate` IS NULL))" +
            " )" +
            " ORDER BY " + orderBy + " " + order
        );               
        
        preparedStatement.setInt(1, patternBean.getEmployeeId());
        preparedStatement.setInt(2, patternBean.getStatusId());
        preparedStatement.setString(3, "%"+(patternBean.getId() == 0 ? "" : patternBean.getId())+"%");
        preparedStatement.setString(4, "%"+patternBean.getText()+"%");
        
        preparedStatement.setDate(5, new java.sql.Date(patternBean.getSearchFromStartDate().getTime()));
        preparedStatement.setDate(6, new java.sql.Date(patternBean.getSearchToStartDate().getTime()));
        
        preparedStatement.setDate(7, new java.sql.Date(patternBean.getSearchFromEndDate().getTime()));
        preparedStatement.setDate(8, new java.sql.Date(patternBean.getSearchToEndDate().getTime()));
        preparedStatement.setDate(9, new java.sql.Date(patternBean.getSearchFromEndDate().getTime()));
        preparedStatement.setDate(10, new java.sql.Date(patternBean.getSearchToEndDate().getTime()));       
        
        resultSet = preparedStatement.executeQuery();                                   
                         
        Collection<TaskBean> result = 
          resultSetToTaskCollection(
            resultSet,
            page,
            itemsPerPage,
            records
          );                                            
                       
        return result;       
      } catch (Exception e){
         handleException(e, "searchTasksByExecutor");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
   
   
      
   /**
    * searchRequests
    * 
    * @param patternBean
    * @param orderBy
    * @param order
    * @param page
    * @param itemsPerPage
    * @param records
    * @return
    */  
   public Collection<RequestBean> searchRequests(
      RequestBean patternBean,
      String orderBy,
      String order,
      int page,
      int itemsPerPage,
      IntWrapper records
   ) {
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {                            
        connection = dataSource.getConnection();               
        order = (order.equals("ASC") ? "ASC" : "DESC");         
        try {         
          RequestOrderByEnum orderEnum = RequestOrderByEnum.valueOf(orderBy);
          switch (orderEnum) {
          case N:
            orderBy = "`request`.`id`";
            break;
          case regnumber:
            orderBy = "`request`.`registration_number`";
            break;
          case client:
            orderBy = "`user`.`lastname`";
            break;
          case organization:  
            orderBy = "`organization`.`shorttitle`";
            break;                    
          case status:
            orderBy = "`requeststatus`.`request_status`";
            break;
          default:
            orderBy = "`request`.`id`";
            break;
          }
        } catch (Exception e) {
          orderBy = "`request`.`id`";
        }
        
        preparedStatement = connection.prepareStatement(
            "SELECT * FROM "+
            " `request`,"+ 
            " `user`," +
            " `organization`," +
            " `requeststatus`" +
            " WHERE `request`.`client_id` = `user`.`id`" +
            " AND `request`.`organization_id` = `organization`.`id`" +
            " AND `request`.`status_id` = `requeststatus`.`id`" +
            " AND (" +
            "  `request`.`clientText` LIKE ?" +
            "  OR `request`.`text` LIKE ?" +
            " )" +
            " AND `requeststatus`.`id` = ?" +
            " AND `request`.`id` LIKE ?" +
            " AND `request`.`registration_number` LIKE ?" +
            " AND CONCAT(user.firstname, user.lastname, user.middlename) LIKE ?" +
            (
              patternBean.getLeadExecutorId() != 0 ? 
              "AND `request`.`leadexecutor_id` = " + patternBean.getLeadExecutorId() : ""
            ) + 
            (
              patternBean.getClientId() != 0 ? 
              "AND `request`.`client_id` = " + patternBean.getClientId() : ""
            ) +
            " AND ((DATE(`request`.`registrationdate`) BETWEEN ? AND ?) OR (`request`.`registrationdate` IS NULL))" +
            " AND " +
            " ( " +
            "   ((DATE(`request`.`realEndDate`) BETWEEN ? AND ?) OR (`request`.`realEndDate` IS NULL))" +
            "   OR " +
            "   ((DATE(`request`.`approxEndDate`) BETWEEN ? AND ?) OR (`request`.`approxEndDate` IS NULL))" +
            " )" +
            " ORDER BY " + orderBy + " " + order
        );
        
        preparedStatement.setString(1, "%"+patternBean.getClientText()+"%");
        preparedStatement.setString(2, "%"+patternBean.getClientText()+"%");
        preparedStatement.setInt(3, patternBean.getRequestStatus().getId());
        preparedStatement.setString(4, "%"+(patternBean.getId() == 0 ? "" : patternBean.getId())+"%");
        preparedStatement.setString(5, "%"+patternBean.getRegNumber()+"%");
        preparedStatement.setString(6, "%"+patternBean.getClient().getFirstName().replaceAll(" ", "%")+"%");
        
        preparedStatement.setDate(7, new java.sql.Date(patternBean.getSearchFromRegistrationDate().getTime()));
        preparedStatement.setDate(8, new java.sql.Date(patternBean.getSearchToRegistrationDate().getTime()));
        
        preparedStatement.setDate(9, new java.sql.Date(patternBean.getSearchFromEndDate().getTime()));
        preparedStatement.setDate(10, new java.sql.Date(patternBean.getSearchToEndDate().getTime()));
        preparedStatement.setDate(11, new java.sql.Date(patternBean.getSearchFromEndDate().getTime()));
        preparedStatement.setDate(12, new java.sql.Date(patternBean.getSearchToEndDate().getTime()));
        
//        System.out.println(preparedStatement.toString());
        
        resultSet = preparedStatement.executeQuery();                                   
                         
        Collection<RequestBean> result = 
          resultSetToCollection(
            resultSet,
            page,
            itemsPerPage,
            records
          );                                            
               
        return result;       
      } catch (Exception e){
         handleException(e, "searchRequests");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
   
   
   
   /**
    * Cancel Request By LeadExecutor
    * @param comment - comment of lead executor
    * @param userId - lead executor id
    * @param requestId - id of request
    * @return
    */
   public boolean cancelRequestByLeadExecutor (
      String comment,
      int userId,
      int requestId 
   ) {
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      
      try {              
        connection = dataSource.getConnection();       
        preparedStatement = connection.prepareStatement(
          "UPDATE `request`" +
          " SET `comment` = ?," +
          " `status_id` = '2'," +
          " `leadexecutor_id` = ?" +
          " WHERE `id` = ?"
        );             
        
        preparedStatement.setString(1, comment);
        preparedStatement.setNull(2, Types.INTEGER);
        preparedStatement.setInt(3, requestId);
               
        int result = preparedStatement.executeUpdate();        
        
        //place new record to history table
        this.downInHistoryRequest(
          requestId, 
          userId
        );               
        
        return (result!=0);  
      } catch (Exception e) {
        handleException(e, "cancelRequestByLeadExecutor");
        throw new EJBException(e);         
     } finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }            
   }
   
   
   
   public Collection<MainReportBean> getMainReport(Date startDate, Date endDate) {
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      ArrayList<MainReportBean> mainReport = 
         new ArrayList<MainReportBean>();
      
      try {                            
        connection = dataSource.getConnection();                       
        
        preparedStatement = connection.prepareStatement(
            "SELECT " +
            " `user`.*," +  
            " `employee_settings`.*, " +
            " COUNT(DISTINCT `finishedtask`.`id`) AS finishedtasks," + 
            " COUNT(DISTINCT `startedtask`.`id`) AS newtasks," +
            " COUNT(DISTINCT `finishedrequest`.`id`) AS finishedrequests," + 
            " COUNT(DISTINCT `startedrequest`.`id`) AS newrequests" +
            " FROM `user`" +
            " LEFT JOIN `employee_settings` ON `employee_settings`.`user_id` = `user`.`id`" +
            " LEFT JOIN `task` AS `finishedtask` ON `finishedtask`.`employee_id` = `user`.`id`" + 
            " AND `finishedtask`.`status_id` = 3 AND `finishedtask`.`enddate` BETWEEN ? AND ?" +
            " LEFT JOIN `task` AS `startedtask` on `startedtask`.`employee_id` = `user`.`id`" +
            " AND `startedtask`.`status_id` IN ('1', '2') AND `startedtask`.`startdate` BETWEEN ? AND ?" +
            " LEFT JOIN `request` AS `finishedrequest` ON `finishedrequest`.`leadexecutor_id` = `user`.`id` " +
            " AND `finishedrequest`.`status_id` = 6 AND `finishedrequest`.`realenddate` BETWEEN ? AND ?" +
            " LEFT JOIN `request` AS `startedrequest` ON `startedrequest`.`leadexecutor_id` = `user`.`id` " +
            " AND `startedrequest`.`status_id` in ('4', '5') AND `startedrequest`.`registrationdate` BETWEEN ? AND ?" +
            " WHERE `employee_settings`.`employeetype_id` IN ('2', '3')" +
            " GROUP BY `user`.`id`" +
            " ORDER BY `user`.`lastname` ASC"
        );
        
        preparedStatement.setDate(1, new java.sql.Date(startDate.getTime()));
        preparedStatement.setDate(2, new java.sql.Date(endDate.getTime()));
        preparedStatement.setDate(3, new java.sql.Date(startDate.getTime()));
        preparedStatement.setDate(4, new java.sql.Date(endDate.getTime()));
        preparedStatement.setDate(5, new java.sql.Date(startDate.getTime()));
        preparedStatement.setDate(6, new java.sql.Date(endDate.getTime()));
        preparedStatement.setDate(7, new java.sql.Date(startDate.getTime()));
        preparedStatement.setDate(8, new java.sql.Date(endDate.getTime()));               
        
        resultSet = preparedStatement.executeQuery();
        
        while (resultSet.next()) {
           MainReportBean mainReportBean = new MainReportBean(resultSet);
           mainReport.add(mainReportBean);
        }
        
        return mainReport;       
      } catch (Exception e){
         handleException(e, "getMainReport");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
      
}